from flask import jsonify, Blueprint, request, current_app
from app.services.organization.organization_service import OrganizationService, organization_service
from app.utils.log_control import INFO, ERROR
from app.infrastructure import db_pool
from app.config.sql_config import SQL_CONFIG, TOTAL_PEOPLE_BY_ORG, FORMAL_PEOPLE_BY_ORG, PROBATION_PEOPLE_BY_ORG, MANAGER_COUNT_BY_ORG  # 添加SQL配置导入
import logging
import traceback
import time
from sqlalchemy import text

# 创建蓝图
api = Blueprint('api', __name__)

# 全局实例，用于蓝图路由
org_service = organization_service

def register_routes(app):
    # 使用全局实例
    #org_service = OrganizationService()
    
    @app.route('/api/organization/info')
    def get_organization_info():
        try:
            # 获取查询参数，默认为1（倍智集团）
            org_id = request.args.get('org_id', type=int, default=1)
            return jsonify(org_service.get_organization_info(belong_id=org_id))
        except Exception as e:
            ERROR.logger.error(f"获取组织信息失败: {str(e)}")
            return jsonify({"status": "error", "message": str(e)}), 500
    
    @app.route('/api/organization/<int:org_id>')
    def get_organization_by_id(org_id):
        try:
            INFO.logger.info(f"通过直接路径访问组织信息，组织ID: {org_id}")
            result = org_service.get_organization_info(belong_id=org_id)
            # 添加data字段包装，确保与前端期望的数据结构一致
            return jsonify({
                "success": True,
                "data": result  # 将结果包装在data字段中
            })
        except Exception as e:
            ERROR.logger.error(f"获取组织信息失败: {str(e)}")
            return jsonify({"success": False, "error": str(e)}), 500
    
    @app.route('/api/organization/overview')
    def get_overview_info():
        try:
            # 获取查询参数，默认为1（倍智集团）
            org_id = request.args.get('org_id', type=int, default=1)
            return jsonify(org_service.get_overview_info(belong_id=org_id))
        except Exception as e:
            ERROR.logger.error(f"获取概览信息失败: {str(e)}")
            return jsonify({"status": "error", "message": str(e)}), 500
    
    @app.route('/api/organization/update', methods=['POST'])
    def update_organization():
        try:
            data = request.json
            result = org_service.update_organization(data)
            return jsonify({"status": "success", "data": result})
        except Exception as e:
            ERROR.logger.error(f"更新组织信息失败: {str(e)}")
            return jsonify({"status": "error", "message": str(e)}), 500


@api.route('/organizations', methods=['GET'])
def get_organizations():
    try:
        INFO.logger.info("正在调用组织列表API")
        print("调用组织列表API")
        
        # 添加额外的错误保护
        try:
            # 使用服务层方法获取组织列表
            organizations = org_service.get_organizations()
            
            print(f"成功获取组织列表，共 {len(organizations)} 个组织")
            # 确保返回的是有效的JSON可序列化数据
            result = []
            for org in organizations:
                # 确保每个字段都是基本类型
                result.append({
                    "id": int(org.get("id", 0)),
                    "name": str(org.get("name", "未命名组织")),
                    "belongId": int(org.get("belongId", 0))
                })
            return jsonify(result)
        except OSError as ose:
            # 特别处理OSError
            ERROR.logger.error(f"OSError in get_organizations: {str(ose)}")
            INFO.logger.info("返回备用组织数据")
            # 备用数据
            fallback_orgs = [
                {"id": 1, "name": "总公司(备用)", "belongId": 1},
                {"id": 2, "name": "分公司A(备用)", "belongId": 2},
                {"id": 3, "name": "分公司B(备用)", "belongId": 3}
            ]
            return jsonify(fallback_orgs)
    except Exception as e:
        error_msg = f"获取组织列表API失败: {str(e)}"
        print(f"错误: {error_msg}")
        ERROR.logger.error(error_msg)
        
        # 返回模拟数据，确保前端不会出错
        mock_orgs = [
            {"id": 1, "name": "总公司(API错误)", "belongId": 1},
            {"id": 2, "name": "分公司A(API错误)", "belongId": 2},
            {"id": 3, "name": "分公司B(API错误)", "belongId": 3}
        ]
        return jsonify(mock_orgs)


@api.route('/organization/<int:org_id>', methods=['GET'])
def get_org_by_id(org_id):
    """通过ID获取组织信息"""
    try:
        INFO.logger.info(f"通过API蓝图访问组织信息，组织ID: {org_id}")
        result = org_service.get_organization_info(belong_id=org_id)
        # 添加data字段包装，确保与前端期望的数据结构一致
        return jsonify({
            "success": True,
            "data": result  # 将结果包装在data字段中
        })
    except Exception as e:
        ERROR.logger.error(f"获取组织信息失败: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500


@api.route('/comparison/total', methods=['GET'])
def get_total_comparison():
    """获取总人数对比数据"""
    try:
        # 获取组织ID参数
        org_id = request.args.get('org_id', type=int, default=1)
        result = org_service.get_total_comparison(belong_id=org_id)
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取总人数对比数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@api.route('/comparison/rank', methods=['GET'])
def get_rank_comparison():
    """获取职级分布对比数据"""
    try:
        # 获取组织ID参数
        org_id = request.args.get('org_id', type=int, default=1)
        result = org_service.get_rank_comparison(belong_id=org_id)
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取职级分布对比数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@api.route('/comparison/all-orgs-total', methods=['GET'])
def get_all_orgs_total_comparison():
    """获取所有组织的总人数对比数据"""
    try:
        result = org_service.get_all_orgs_total_comparison()
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取所有组织总人数对比数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@api.route('/api/db/data')
def get_db_data():
    """获取数据库数据"""
    try:
        # 获取org_id参数
        org_id = request.args.get('org_id', '1')
        INFO.logger.info(f"获取数据库数据，组织ID: {org_id}")
        
        # 详细日志记录
        INFO.logger.info("开始获取数据库指标数据")
        INFO.logger.info("调用organization_service.get_organization_db_metrics")
        
        # 获取数据库指标
        from app.services.organization.organization_service import organization_service
        db_metrics = organization_service.get_organization_db_metrics(org_id)
        
        # 记录结果
        INFO.logger.info(f"获取到数据库指标: {db_metrics}")
        INFO.logger.info(f"数据库总人数: {db_metrics.get('total_count')}")
        INFO.logger.info(f"数据库总人数类型: {type(db_metrics.get('total_count')).__name__}")
        
        # 包装结果为标准格式
        result = {
            'success': True,
            'data': db_metrics
        }
        
        INFO.logger.info(f"返回数据库数据: {result}")
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取数据库数据失败: {str(e)}\n{traceback.format_exc()}")
        return jsonify({
            'success': False,
            'error': str(e),
            'message': "获取数据库数据失败"
        }), 500


@api.route('/api-data', methods=['GET'])
def get_api_data():
    """获取API数据"""
    try:
        # 这里暂时使用get_organization_info作为替代
        result = org_service.get_organization_info()
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取API数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@api.route('/test-db', methods=['GET'])
def test_db_connection():
    """测试数据库连接并返回诊断信息"""
    result = {
        "status": "unknown",
        "steps": [],
        "connection_info": {}
    }
    
    # 步骤1: 获取数据库连接配置
    try:
        from app.config.api_config import DB_QUERY_CONFIG
        result["steps"].append({"step": 1, "status": "success", "message": "成功获取数据库配置"})
        # 过滤掉敏感信息
        connection_info = {
            "host": DB_QUERY_CONFIG.get("host"),
            "port": DB_QUERY_CONFIG.get("port"),
            "database": DB_QUERY_CONFIG.get("database"),
            "user": DB_QUERY_CONFIG.get("user"),
            # 不返回密码
        }
        result["connection_info"] = connection_info
    except Exception as e:
        result["steps"].append({"step": 1, "status": "error", "message": f"获取数据库配置失败: {str(e)}"})
        return jsonify(result), 500
    
    # 步骤2: 尝试连接数据库
    try:
        conn = db_pool.get_connection()
        result["steps"].append({"step": 2, "status": "success", "message": "数据库连接成功"})
    except Exception as e:
        result["steps"].append({"step": 2, "status": "error", "message": f"数据库连接失败: {str(e)}"})
        result["status"] = "error"
        return jsonify(result), 500
    
    # 步骤3: 尝试执行简单查询
    try:
        with conn.cursor() as cursor:
            # 测试查询1: 检查sys_org表是否存在
            try:
                cursor.execute("SHOW TABLES LIKE 'sys_org'")
                table_exists = len(cursor.fetchall()) > 0
                message = "sys_org表存在" if table_exists else "sys_org表不存在"
                status = "success" if table_exists else "warning"
                result["steps"].append({"step": 3.1, "status": status, "message": message})
                
                # 如果表存在，则尝试查询数据
                if table_exists:
                    cursor.execute("SELECT COUNT(*) FROM sys_org")
                    count = cursor.fetchone()[0]
                    result["steps"].append({
                        "step": 3.2, 
                        "status": "success", 
                        "message": f"sys_org表中有 {count} 条记录"
                    })
                    
                    # 尝试获取一条记录
                    cursor.execute("SELECT id, name FROM sys_org LIMIT 1")
                    sample = cursor.fetchone()
                    if sample:
                        result["steps"].append({
                            "step": 3.3, 
                            "status": "success", 
                            "message": f"成功获取示例记录: ID={sample[0]}, Name={sample[1]}"
                        })
                    else:
                        result["steps"].append({
                            "step": 3.3, 
                            "status": "warning", 
                            "message": "表中没有记录"
                        })
            except Exception as table_err:
                result["steps"].append({
                    "step": 3, 
                    "status": "error", 
                    "message": f"查询sys_org表失败: {str(table_err)}"
                })
    except Exception as query_err:
        result["steps"].append({"step": 3, "status": "error", "message": f"执行查询失败: {str(query_err)}"})
    finally:
        try:
            conn.close()
            result["steps"].append({"step": 4, "status": "success", "message": "数据库连接已关闭"})
        except:
            pass
    
    # 设置最终状态
    if all(step["status"] == "success" for step in result["steps"]):
        result["status"] = "success"
    elif any(step["status"] == "error" for step in result["steps"]):
        result["status"] = "error"
    else:
        result["status"] = "warning"
    
    return jsonify(result)

# 添加一个简单的测试路由，不依赖任何服务
@api.route('/test', methods=['GET'])
def test_route():
    logging.info("测试路由被调用")
    return jsonify({"status": "success", "message": "测试路由工作正常"})

@api.route('/full-api-data', methods=['GET'])
def get_full_api_data():
    """获取组织的完整API数据"""
    try:
        # 获取组织ID参数
        org_id = request.args.get('org_id', type=int, default=1)
        
        # 获取完整的API数据
        api_data = org_service.get_organization_info(belong_id=org_id, use_cache=False)
        
        # 从服务层获取组织名称
        orgs = org_service.get_organizations()
        org_name = next((org['name'] for org in orgs if org['belongId'] == org_id), f"组织{org_id}")
        
        result = {
            'success': True,
            'data': {
                'belong_id': org_id,
                'org_name': org_name,
                'api_data': api_data
            }
        }
        
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取完整API数据失败: {str(e)}")
        import traceback
        ERROR.logger.error(traceback.format_exc())
        return jsonify({
            'success': False, 
            'error': str(e)
        }), 500

@api.route('/api/debug/total-people', methods=['GET'])
def debug_total_people():
    """调试查询总人数"""
    try:
        # 获取组织ID参数
        org_id = request.args.get('org_id', type=int, default=1)
        
        # 开始时间
        start_time = time.time()
        
        # 记录请求信息
        INFO.logger.info(f"开始处理/api/debug/total-people请求，org_id={org_id}")
        
        # 1. 尝试使用原始SQL查询方式
        sql_query = TOTAL_PEOPLE_BY_ORG
        INFO.logger.info(f"使用SQL查询: {sql_query}")
        
        # 查询参数
        params = {"belong_id": org_id}
        INFO.logger.info(f"查询参数: {params}")
        
        # 执行查询
        with db_pool.get_connection() as conn:
            # 转换为SQLAlchemy text对象
            sql = text(sql_query)
            
            # 执行查询
            result = conn.execute(sql, params)
            
            # 获取结果
            row = result.fetchone()
            
            # 分析返回值
            if row:
                INFO.logger.info(f"查询结果: {row}")
                
                # 检查行类型
                row_type = type(row).__name__
                INFO.logger.info(f"行类型: {row_type}")
                
                # 检查是否有_mapping属性
                has_mapping = hasattr(row, '_mapping')
                INFO.logger.info(f"是否有_mapping属性: {has_mapping}")
                
                # 尝试不同的访问方式
                if has_mapping:
                    mapping_dict = dict(row._mapping)
                    INFO.logger.info(f"_mapping字典: {mapping_dict}")
                
                # 尝试索引访问
                try:
                    first_value = row[0]
                    INFO.logger.info(f"索引0的值: {first_value}")
                except Exception as e:
                    INFO.logger.error(f"索引访问失败: {str(e)}")
                
                # 尝试转换为字典 - 修复转换方式
                try:
                    # 检查是否有_asdict方法 (RowProxy可能有这个方法)
                    if hasattr(row, '_asdict'):
                        row_dict = row._asdict()
                    # 检查是否已经获取了mapping
                    elif hasattr(row, '_mapping'):
                        row_dict = dict(row._mapping)
                    # 尝试将行对象的键值对转换为字典
                    else:
                        # 创建一个列名:值的字典
                        row_dict = {col: getattr(row, col) for col in row.keys()} if hasattr(row, 'keys') else {"value": row[0]}
                    
                    INFO.logger.info(f"转换为字典: {row_dict}")
                except Exception as e:
                    ERROR.logger.error(f"转换为字典失败，尝试替代方法: {str(e)}")
                    # 最后的后备方案，只记录索引值
                    try:
                        row_dict = {"total_count": row[0]}
                        INFO.logger.info(f"使用后备方法转换为字典: {row_dict}")
                    except Exception as e2:
                        ERROR.logger.error(f"后备方法也失败: {str(e2)}")
            else:
                INFO.logger.info("查询未返回结果")
        
        # 结束时间
        end_time = time.time()
        execution_time = round((end_time - start_time) * 1000)
        
        return jsonify({
            'success': True,
            'message': "调试信息已记录到日志",
            'execution_time_ms': execution_time
        })
    except Exception as e:
        ERROR.logger.error(f"调试总人数查询失败: {str(e)}")
        import traceback
        ERROR.logger.error(traceback.format_exc())
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

@api.route('/comparison/gender-ratio', methods=['GET'])
def get_gender_ratio_comparison():
    """获取性别比例对比数据"""
    try:
        # 获取组织ID参数
        org_id = request.args.get('org_id', type=int)
        
        # 调用服务方法获取对比数据
        result = org_service.get_gender_ratio_comparison(belong_id=org_id)
        
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取性别比例对比数据失败: {str(e)}")
        import traceback
        ERROR.logger.error(traceback.format_exc())
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

@api.route('/api/gender-ratio/db', methods=['GET'])
def get_gender_ratio_db():
    """获取数据库中的性别比例数据"""
    try:
        # 获取组织ID参数
        org_id = request.args.get('org_id', type=int)
        
        # 调用服务方法获取数据库数据
        result = org_service.get_gender_ratio_db(belong_id=org_id)
        
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取数据库性别比例数据失败: {str(e)}")
        import traceback
        ERROR.logger.error(traceback.format_exc())
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

@api.route('/api/gender-ratio/api', methods=['GET'])
def get_gender_ratio_api():
    """获取API中的性别比例数据"""
    try:
        # 获取组织ID参数
        org_id = request.args.get('org_id', type=int)
        
        # 调用服务方法获取API数据
        result = org_service.get_gender_ratio_api(org_id)
        
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取API性别比例数据失败: {str(e)}")
        import traceback
        ERROR.logger.error(traceback.format_exc())
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

# 使用中文路由名称以便于前端开发人员理解
@api.route('/api/性别比例/对比', methods=['GET'])
def get_gender_ratio_comparison_zh():
    """获取性别比例对比数据（中文路由）"""
    return get_gender_ratio_comparison()

@api.route('/api/性别比例/数据库', methods=['GET'])
def get_gender_ratio_db_zh():
    """获取数据库中的性别比例数据（中文路由）"""
    return get_gender_ratio_db()

@api.route('/api/性别比例/接口', methods=['GET'])
def get_gender_ratio_api_zh():
    """获取API中的性别比例数据（中文路由）"""
    return get_gender_ratio_api()

@api.route('/all-orgs-total-comparison', methods=['GET'])
def get_all_orgs_total_comparison_alt():
    """获取所有组织的总人数对比数据（替代路径）"""
    try:
        result = org_service.get_all_orgs_total_comparison()
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取所有组织总人数对比数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500
